﻿using CreateCodeByDatabase.Dal;
using CreateCodeByDatabase.Model;
using ReadModel;
using ReadModel.MSIL;
using System;
using System.Collections.Generic;
using System.Data;
using System.Globalization;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;

namespace CreateCodeByDatabase.Bll
{
    public class Operate_Sql:IOperate
    {
        /// <summary>
        /// 根据合约号查询园机入库信息
        /// </summary>
        /// <param name="hyh">合约号</param>
        /// <returns></returns>
        public override IEnumerable<TableInfo> QueryTableList(string tableName)
        {
            string sql = "select b.value Comment,a.name TableName from sys.tables a left join sys.extended_properties b on a.object_id = b.major_id and b.minor_id = 0";
            if (!string.IsNullOrWhiteSpace(tableName))
            {
                sql += " where Upper(a.name) like '%" + tableName.ToUpper() + "%'";
            }
            sql += " order by a.name";
            using (DapperHelper help = new DapperHelper())
            {
                try
                {
                    return help.DbContext.Query<TableInfo>(sql);
                }
                catch (Exception ex)
                {
                    Log.WriteLog(ex);
                    return null;
                }
            }
        }
        //public override IEnumerable<TableInfo> QueryTableList1()
        //{
        //    string sql = "select b.value Comment,a.name TableName from sys.tables a left join sys.extended_properties b on a.object_id = b.major_id and b.minor_id = 0";
        //    sql += " order by a.name";
        //    using (DapperHelper help = new DapperHelper())
        //    {
        //        try
        //        {
        //            IDataReader reader = help.DbContext.ExecuteReader(sql);
        //            Type T = typeof(TableInfo);
        //            var entityList = Query<TableInfo>(reader).ToList();
        //            return entityList;
        //        }
        //        catch (Exception ex)
        //        {
        //            Log.WriteLog(ex);
        //            return null;
        //        }
        //    }
        //}
        //public override IEnumerable<TableInfo> QueryTableList2()
        //{
        //    string sql = "select b.value Comment,a.name TableName from sys.tables a left join sys.extended_properties b on a.object_id = b.major_id and b.minor_id = 0";
        //    sql += " order by a.name";
        //    using (DapperHelper help = new DapperHelper())
        //    {
        //        try
        //        {
        //            IDataReader reader = help.DbContext.ExecuteReader(sql);
        //            Type T = typeof(TableInfo);
        //            var entityList = ReflectionTest.GetEntityList<TableInfo>(reader).ToList();
        //            return entityList;
        //        }
        //        catch (Exception ex)
        //        {
        //            Log.WriteLog(ex);
        //            return null;
        //        }
        //    }
        //}
        /// <summary>
        /// 获取指定表列信息
        /// </summary>
        /// <param name="tableNameList">指定表名集合</param>
        /// <returns></returns>
        public override IEnumerable<ColumnInfo> GetTableColumnList(IEnumerable<string> tableNameList)
        {
            string sqlTemplate = "SELECT c.name as TableName,a.name as ColumnName,Upper(b.name) DataType,COLUMNPROPERTY(a.id,a.name,'Precision') DataLength,isnull(COLUMNPROPERTY(a.id,a.name,N'Scale'),0) DataPrecision,";
            sqlTemplate += "isnull(e.[value],'') as Comment FROM syscolumns a left join systypes b on a.xusertype = b.xusertype";
            sqlTemplate += " inner join sysobjects c on a.id = c.id and c.xtype  ='u' and c.name != 'dtproperties'";
            sqlTemplate += " left join sys.extended_properties d on c.id=d.major_id and d.minor_id=0";
            sqlTemplate += " left join sys.extended_properties e on a.id=e.major_id and a.colid=e.minor_id";
            sqlTemplate += " where c.name = '{0}' order by a.colid";
            using (DapperHelper help = new DapperHelper())
            {
                string sql = null;
                try
                {
                    List<ColumnInfo> entityList = new List<ColumnInfo>();
                    foreach (string item in tableNameList)
                    {
                        sql = string.Format(sqlTemplate, item);
                        entityList.AddRange(help.DbContext.Query<ColumnInfo>(sql));
                    }
                    return entityList;
                }
                catch (Exception ex)
                {
                    Log.WriteLog(ex);
                    return null;
                }
            }
        }
        public override int ExecuteSql(string sql, List<string> aloneExecuteSqlList = null)
        {
            if (string.IsNullOrEmpty(sql))
                return 0;
            using (DapperHelper help = new DapperHelper())
            {
                try
                {
                    help.DbContext.Execute(sql);
                    if (aloneExecuteSqlList != null && aloneExecuteSqlList.Count > 0)
                    {
                        foreach (var item in aloneExecuteSqlList)
                        {
                            help.DbContext.Execute(item);
                        }
                    }
                    return 1;
                }
                catch (Exception ex)
                {
                    Log.WriteLog(ex);
                    return 0;
                }
            }
        }
    }
}
